package com.yzycoc.hamster.demo.time202505.time20250527.test;

import cn.hutool.core.io.resource.ClassPathResource;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.DbUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.ds.simple.SimpleDataSource;
import cn.hutool.db.handler.BeanHandler;
import com.alibaba.fastjson2.JSON;
import com.yzycoc.hamster.demo.common.DistrictAll;
import com.yzycoc.hamster.demo.time202505.time20250517.info.DisasterPointInfo;
import com.yzycoc.hamster.demo.time202505.time20250520.info.ThreatPoint;
import com.yzycoc.hamster.demo.time202505.time20250527.info.DataWeatherStationInfo;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.IOException;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * hamster-demo
 *
 * @author Administrator
 * @since 2025/5/27 20:30
 **/
public class Main {

    public static void main(String[] args) {
        try {
            Main.start();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void start() throws SQLException, IOException {
        // 1. 创建数据源
        SimpleDataSource dataSource = new SimpleDataSource(
                "jdbc:mysql://localhost:3306/xc_cloud?useSSL=false&serverTimezone=Asia/Shanghai",
                "root",
                "root"
        );
        Db db = DbUtil.use(dataSource);
        List<DataWeatherStationInfo> dbList = db.query("select * from data_weather_station_info", DataWeatherStationInfo.class);
        List<DataWeatherStationInfo> excelList = Main.readExcel();

        Map<String, DataWeatherStationInfo> dbMap = dbList.stream().collect(Collectors.toMap(DataWeatherStationInfo::getStationIdC, a -> a, (a, b) -> a));

        List<DataWeatherStationInfo> addList = new ArrayList<>();
        for (DataWeatherStationInfo excel : excelList) {
            if(StrUtil.isEmpty(excel.getStationIdC())){
                continue;
            }
            DataWeatherStationInfo dataWeatherStationInfo = dbMap.get(excel.getStationIdC());
            if(dataWeatherStationInfo == null){
                DistrictAll dis = db.
                        query("select * from district_all where name = ? and level = 3",
                                new BeanHandler<>(DistrictAll.class),excel.getCnty());
                if(dis == null){
                    System.out.println("未找到" + JSON.toJSONString(excel));
                    return;
                }
                String stationName = excel.getStationName();
                stationName = stationName.replace("气象观测站","");
                excel.setStationName(stationName);
                excel.setAdminCodeChn(dis.getCode());
                excel.setTownCode("999999");
                excel.setCountry("中国");
                excel.setStationLevl("14");
                excel.setStationType(2);
                excel.setStatus(1);
                excel.setCreateTime(new Date());
                excel.setUpdateTime(new Date());
                addList.add(excel);
            }
        }

        //List<DataWeatherStationInfo> addDbList = new ArrayList<>();
        //Map<String, DataWeatherStationInfo> excelMap = excelList.stream().collect(Collectors.toMap(DataWeatherStationInfo::getStationIdC, a -> a, (a, b) -> a));
        //for (DataWeatherStationInfo info : dbList) {
        //    if(StrUtil.isEmpty(info.getStationIdC())){
        //        continue;
        //    }
        //    DataWeatherStationInfo dataWeatherStationInfo = excelMap.get(info.getStationIdC());
        //    if(dataWeatherStationInfo == null){
        //        addDbList.add(info);
        //    }
        //}
        for (DataWeatherStationInfo info : addList) {
            try {
                db.insert(
                        Entity.create("data_weather_station_info")
                                .set("station_id_c", info.getStationIdC())
                                .set("station_name", info.getStationName())
                                .set("admin_code_chn", info.getAdminCodeChn())
                                .set("town_code", info.getTownCode())
                                .set("country", info.getCountry())
                                .set("province", info.getProvince())
                                .set("city", info.getCity())
                                .set("cnty", info.getCnty())
                                .set("town", info.getTown())
                                .set("station_levl", info.getStationLevl())
                                .set("station_type", info.getStationType())
                                .set("lat", info.getLat())
                                .set("lon", info.getLon())
                                .set("alti", info.getAlti())
                                .set("create_time", info.getCreateTime())
                                .set("update_time", info.getUpdateTime())
                                .set("status", info.getStatus())
                                .set("department", info.getDepartment())
                                .set("station_address", info.getStationAddress())
                );
            } catch (Exception e) {
                System.err.println("插入失败：" + info);
                e.printStackTrace();
            }
        }

        System.out.println("总共需要新增的数据：" + addList.size());
    }

    private static List<DataWeatherStationInfo> readExcel() throws IOException {
        List<DataWeatherStationInfo> result = new ArrayList<>();
        ClassPathResource resource = new ClassPathResource("static/20250527/黄山数据.xlsx");
        InputStream inputStream = resource.getStream();
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 默认读取第一个 sheet
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;

            DataWeatherStationInfo info = new DataWeatherStationInfo();
            int col = 0;
            info.setProvince(getCellStringValue(row.getCell(col++)));
            info.setCity(getCellStringValue(row.getCell(col++)));
            info.setCnty(getCellStringValue(row.getCell(col++)));
            info.setStationName(getCellStringValue(row.getCell(col++)));
            info.setStationIdC(getCellStringValue(row.getCell(col++)));
            info.setLon(getCellStringValue(row.getCell(col++)));
            info.setLat(getCellStringValue(row.getCell(col++)));
            result.add(info);
        }
        workbook.close();
        return result;
    }

    /**
     * 数据
     */
    private static String getCellStringValue(Cell cell) {
        if (cell == null) return null;
        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue().trim();
    }

    /**
     * 数据值
     */
    private static Integer getCellIntegerValue(Cell cell) {
        if (cell == null) return null;
        try {
            return (int) cell.getNumericCellValue();
        } catch (Exception e) {
            try {
                return Integer.valueOf(cell.getStringCellValue().trim());
            } catch (Exception ex) {
                return null;
            }
        }
    }
}
